
package com.huizhan.web.dao;
 
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;

import java.util.List;

import javax.transaction.Transactional;

import com.huizhan.web.model.*;

@Repository 
public interface DocumentDao extends JpaRepository<Document,Long>
{
    
     /**
      * 根据项目id获取通行规则数量
      * @param projectId  项目id
      * @return 数量
      */
      Integer countByProjectId(String projectId);
    
     /**
      * 根据项目id获取通行规则数量
      * @param projectId  项目id
      * @return 数量
      */
      Integer countByProjectIdAndCode(String projectId,String code);
    
     /**
      * 根据项目id获取通行规则数量
      * @param projectId  项目id
      * @return 数量
      */
     List<Document> findByProjectId(String projectId);
    
     /**
      * 根据项目id获取通行规则数量
      * @param projectId  项目id
      * @return 数量
      */
      Document findByProjectIdAndCode(String projectId,String code);
    
     /**
      * 根据项目id获取通行规则数量
      * @param projectId  项目id
      * @return 数量
      */
      Document findByDocumentTypeIdAndPhone(Long documentTypeId,String phone);
    
     /**
      * 根据类型id获取证件数量
      * @param projectId  类型id
      * @return 数量
      */
    Integer countByDocumentTypeId(Long documentTypeId);
    
    /**
     * 根据分发记录删除证件
     * @param distributeId
     */
    @Transactional
    void deleteByDistributeId(Long distributeId);

    /**
     * 根据类型设置头像
     *
     */
    @Transactional
    @Modifying
    @Query(value =  "update document  set avatar='' where  document_Type_Id=?1",nativeQuery = true)
    Integer updateByDocumentTypeId(Long documentTypeId);

    /**
     * 分页查证件记录集合
     * @param name 名称
     * @param code 码值
     * @param document_Type_Id 证件类型ID
     * @param project_Id 项目ID
     * @param idNumber 编号
     * @param pageable 分页参数
     * @return 赠票记录集合
     */
    @Query(
        value = "SELECT * FROM document"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (code LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 ) "
            + " AND (document_Type_Id =?4 OR ?4 = 0 )"
            + " AND (id_Number LIKE CONCAT('%',?5,'%') OR ?5  = '' )", 
        countQuery = "SELECT COUNT(1) FROM document"
            + " WHERE (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (code LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 ) "
            + " AND (document_Type_Id =?4 OR ?4 = 0 )"
            + " AND (id_Number LIKE CONCAT('%',?5,'%') OR ?5  = '' )",
        nativeQuery = true
    )
    Page<Document> findByWhere(String name,String code, String project_Id,Long document_Type_Id,String idNumber, Pageable  pageable);
}
